from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession
from typing import List, Tuple
import math

from app.crud.base import CRUDBase
from app.models.file import UploadedFile
from app.schemas.file import UploadedFileCreate

class CRUDFile(CRUDBase[UploadedFile, UploadedFileCreate, None]):
    async def count_by_user(self, db: AsyncSession, *, user_id: str) -> int:
        """Count files uploaded by a specific user"""
        result = await db.execute(
            select(func.count(UploadedFile.id)).filter(UploadedFile.user_id == user_id)
        )
        return result.scalar() or 0
    
    async def get_user_files_paginated(
        self, 
        db: AsyncSession, 
        *, 
        user_id: str, 
        page: int = 1, 
        page_size: int = 20
    ) -> Tuple[List[UploadedFile], int]:
        """Get paginated files for a specific user"""
        # Calculate offset
        offset = (page - 1) * page_size
        
        # Get total count
        count_result = await db.execute(
            select(func.count(UploadedFile.id)).filter(UploadedFile.user_id == user_id)
        )
        total = count_result.scalar() or 0
        
        # Get paginated results
        files_result = await db.execute(
            select(UploadedFile)
            .filter(UploadedFile.user_id == user_id)
            .order_by(UploadedFile.created_at.desc())
            .offset(offset)
            .limit(page_size)
        )
        files = files_result.scalars().all()
        
        return list(files), total

crud_file = CRUDFile(UploadedFile)
